package com.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.demo.entity.Oil;
import com.demo.entity.Speed;
import org.apache.ibatis.annotations.*;
import org.apache.tomcat.util.http.fileupload.util.LimitedInputStream;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@Mapper
public interface OilMapper extends BaseMapper<Oil> {



    /**
     * 查询返回所有油田编号（oil_field）+经纬度，每个油田编号只返回一条。
     *
     * @return
     */
    @Select("SELECT oil_field, longitude, latitude FROM nodedata_table where id in (select max(id) from nodedata_table group by oil_field)")
    ArrayList<Map<String, Object>> selectDistinctOilFieldWithLocation();


    /**
     * 根据指定的油田编号和节点编号，返回指定节点的最新数据。
     *
     * @param oil_field
     * @param eui
     * @return
     */
    @Select("SELECT * FROM nodedata_table WHERE oil_field = #{oil_field} AND eui = #{eui} ORDER BY `time` DESC LIMIT 1")
    Oil getLatestDataByFieldAndNode(@Param("oil_field") String oil_field, @Param("eui") String eui);


    /**
     * 根据根据指定的油田编号，返回该由田中所有节点的最新数据
     *
     * @param oil_field
     * @return
     */
    @Select("SELECT * FROM nodedata_table WHERE oil_field = #{oil_field} AND `time` IN (SELECT MAX(`time`) FROM nodedata_table WHERE oil_field = #{oil_field} GROUP BY eui)")
    List<Oil> getLatestDataByOilField(String oil_field);

    /**
     * 根据指定的油田编号和节点编号，返回所有数据。
     *
     * @param oil_field
     * @param eui
     * @return
     */
    @Select("SELECT * FROM nodedata_table WHERE oil_field = #{oil_field} AND eui = #{eui}")
    List<Oil> getDataByOilFieldAndNode(@Param("oil_field") String oil_field, @Param("eui") String eui);

    /**
     * 更新wifi状态
     */
    @Update("UPDATE nodedata_table SET wifi_status = #{wifi_status} WHERE oil_field = #{oil_field} AND eui = #{eui} ORDER BY `time` DESC LIMIT 1")
    void updateWiFiStatusByFieldAndNode(@Param("oil_field") String oil_field, @Param("eui") String eui, @Param("wifi_status") boolean wifi_status);

    //@Insert("INSERT INTO nodedata_table VALUES ( #{oilfield}, #{eui}, #{time}, #{voltage}, #{memory}, #{longitude}, #{latitude}, #{wifistatus}, #{nodefault})")
    // void insertOil(@Param("oil_field") String oilfield,@Param("eui") String eui,@Param("time") String time,@Param("voltage") float voltage,@Param("memory") int memory,@Param("longitude") float longitude,@Param("latitude") float latitude,@Param("wifi_status") boolean wifistatus,@Param("node_fault") boolean nodefault);

//        @Insert("INSERT INTO nodedata_table (oil_field, eui, time, voltage, memory, longitude, latitude, wifistatus, nodefault) VALUES (#{oilField}, #{eui}, #{time}, #{voltage}, #{memory}, #{longitude}, #{latitude}, #{wifistatus}, #{nodefault})")
//        void insertOil(Oil oil);

//        @Insert("INSERT INTO nodedata_table (oil_field, eui, time, voltage, memory, longitude, latitude, wifistatus, nodefault) " +
//                "SELECT #{oil_field}, #{eui}, #{time}, " +
//                "(SELECT voltage FROM nodedata_table WHERE oil_field = #{oil_field} AND eui = #{eui} ORDER BY time DESC LIMIT 1), " +
//                "(SELECT memory FROM nodedata_table WHERE oil_field = #{oil_field} AND eui = #{eui} ORDER BY time DESC LIMIT 1), " +
//                "(SELECT longitude FROM nodedata_table WHERE oil_field = #{oil_field} AND eui = #{eui} ORDER BY time DESC LIMIT 1), " +
//                "(SELECT latitude FROM nodedata_table WHERE oil_field = #{oil_field} AND eui = #{eui} ORDER BY time DESC LIMIT 1), " +
//                "#{wifistatus}, " +
//                "(SELECT nodefault FROM nodedata_table WHERE oil_field = #{oil_field} AND eui = #{eui} ORDER BY time DESC LIMIT 1)")
//        void insertOil(@Param("oil_field") String oil_field, @Param("eui") String eui, @Param("time") String time, @Param("wifi_status") boolean wifi_status);


    // void insertOil(Oil oil);

    /**
     * 根据指定油厂编号与节点查询节点状态node_fault（默认值为false）
     *
     * @param oil_field
     * @param eui
     * @return
     */
    @Select("SELECT node_fault FROM nodedata_table WHERE oil_field = #{oil_field} AND eui = #{eui} ORDER BY `time` DESC LIMIT 1")
    int getNodeFaultByFieldAndNode(@Param("oil_field") String oil_field, @Param("eui") String eui);


    /**
     * 根据指定的油厂编号、节点ID和节点状态查询最新的节点信息
     *
     * @param oil_field
     * @param eui
     * @param node_fault
     * @return
     */
    List<Oil> getLatestDataByFieldAndNodeAndFault(@Param("oil_field") String oil_field, @Param("eui") String eui, @Param("node_fault") Integer node_fault);

    /**
     * /**
     * 返回油厂数量
     *
     * @return
     */
    @Select("SELECT COUNT(DISTINCT oil_field) FROM nodedata_table")
    int countOilFields();

    /**
     * 返回所有节点总数（注意由于更新时间设计）分油厂给数据
     *
     * @return
     */
    @Select("SELECT oil_field, COUNT(DISTINCT eui) AS node_count " +
            "FROM nodedata_table " +
            "WHERE (oil_field, time) IN (SELECT oil_field, MAX(time) FROM nodedata_table GROUP BY oil_field) " +
            "GROUP BY oil_field")
    List<Map<String, Object>> getNodeCountByOilField();

    @Select("SELECT COUNT(DISTINCT eui) FROM nodedata_table")
    int counteuis();


    /**
     * 返回正常节点总数量，返回异常节点总数量（节点总数 = 正常节点 + 异常节点），均为最新时间
     *
     * @return
     */
    @Select("SELECT oil_field, " +
            "       SUM(CASE WHEN node_fault = 0 THEN 1 ELSE 0 END) AS normal_node_count, " +
            "       SUM(CASE WHEN node_fault = 1 THEN 1 ELSE 0 END) AS fault_node_count " +
            "FROM (SELECT DISTINCT oil_field, eui, node_fault, `time` FROM nodedata_table) AS distinct_data " +
            "WHERE (oil_field, time) IN (SELECT oil_field, MAX(`time`) FROM nodedata_table GROUP BY oil_field) " +
            "GROUP BY oil_field")
    List<Map<String, Object>> getNodeCountsByFault();

    @Select("SELECT COUNT(DISTINCT(eui)) FROM nodedata_table WHERE node_fault = 1")
    Integer getTotalNormalNodeCount();

    @Select("SELECT COUNT(DISTINCT(eui)) FROM nodedata_table WHERE node_fault != 1")
    Integer getTotalFaultNodeCount();


    @Select("SELECT * FROM nodedata_table WHERE oil_field = #{oil_field} AND node_fault != 1 AND `time` IN (SELECT MAX(`time`) FROM nodedata_table WHERE oil_field = #{oil_field} GROUP BY eui)")
    List<Oil> getFaultInfo(@Param("oil_field") String oil_field, @Param("node_fault") boolean node_fault);


}